package com.itheima02.dao;

import com.itheima02.domain.Student;
import com.itheima02.utils.JDBCUtils;


import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class StudentDaoImpl implements StudentDao {
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        Statement stat = null;
        ResultSet rs =null;
        try {
           /* Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db8","root","root");*/
            JDBCUtils.getConnection();
            stat = con.createStatement();
            String sql = "SELECT * FROM Student";
            rs = stat.executeQuery(sql);

            //处理结果集
            while(rs.next()){
               Integer id = rs.getInt("id");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                Student stu = new Student(id, name, age, birthday);

                list.add(stu);

            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           /* try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }*/
           JDBCUtils.close(con,stat,rs);
        }
        return list;
    }

    @Override
    public Student findById(Integer id) {
        Student stu = new Student();
        Connection con = null;
        Statement stat = null;
        ResultSet rs =null;
        try {
           /* Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db8","root","root");*/
            JDBCUtils.getConnection();
            stat = con.createStatement();
            String sql = "SELECT * FROM Student where id='"+id+"'";
            rs = stat.executeQuery(sql);

            //处理结果集
            while(rs.next()){
                Integer sid = rs.getInt("id");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                stu.setId(sid);
                stu.setName(name);
                stu.setAge(age);
                stu.setDate(birthday);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
         /*   try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }*/
         JDBCUtils.close(con,stat,rs);
        }
        return stu;
    }

    @Override
    public int insert(Student stu) {
        int result = 0;
        Connection con = null;
        Statement stat = null;
        try {
           /* Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db8","root","root");*/
            JDBCUtils.getConnection();
            stat = con.createStatement();
            Date date = stu.getDate();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(date);

            String sql = "insert into student values ('"+stu.getId()+"','"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
            result = stat.executeUpdate(sql);




        } catch (Exception e) {
            e.printStackTrace();
        }finally {
        /*    try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }*/
        JDBCUtils.close(con,stat);
        }
        return result;
    }

    @Override
    public int delete(Integer id) {
        int result = 0;
        Connection con = null;
        Statement stat = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db8","root","root");
            stat = con.createStatement();

            String sql = "delete from student where id='"+id+"'";
            result = stat.executeUpdate(sql);



        } catch (Exception e) {
            e.printStackTrace();
        }finally {
          /*  try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }*/
          JDBCUtils.close(con,stat);
        }
        return result;
    }


    @Override
    public int update(Student stu) {
        int result = 0;
        Connection con = null;
        Statement stat = null;
        try {
          /*  Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db8","root","root");*/
            JDBCUtils.getConnection();
            stat = con.createStatement();
            Date date = stu.getDate();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(date);

            String sql = "update student set id='"+stu.getId()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' where id = '"+stu.getId()+"'";
            result = stat.executeUpdate(sql);



        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           /* try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }*/
           JDBCUtils.close(con,stat);
        }
        return result;
    }
}
